// 留言与订阅管理API
const express = require('express');
const router = express.Router();
const db = require('../config/database');

// 获取留言列表
router.get('/messages', async (req, res) => {
    try {
        const { page = 1, limit = 10, search = '' } = req.query;
        const offset = (page - 1) * limit;
        
        let whereClause = '';
        let queryParams = [];
        
        if (search) {
            whereClause = `WHERE m.name LIKE ? OR m.email LIKE ? OR m.content LIKE ? OR m.subject LIKE ? OR m.phone LIKE ?`;
            queryParams = [`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`];
        }
        
        // 获取总数
        const [totalResult] = await db.query(
            `SELECT COUNT(*) as total FROM messages m ${whereClause}`,
            queryParams
        );
        
        // 获取数据 - 包含完整的客户信息
        const [messages] = await db.query(
            `SELECT 
                m.*,
                u.name as customer_name,
                u.email as customer_email,
                u.phone as customer_phone,
                u.mobile as customer_mobile,
                u.address as customer_address,
                u.city as customer_city,
                u.country as customer_country
            FROM messages m
            LEFT JOIN users u ON m.email = u.email
            ${whereClause} ORDER BY m.created_at DESC LIMIT ? OFFSET ?`,
            [...queryParams, parseInt(limit), offset]
        );
        
        res.json({
            success: true,
            data: {
                messages: messages,
                total: totalResult.total,
                page: parseInt(page),
                totalPages: Math.ceil(totalResult.total / limit)
            }
        });
    } catch (error) {
        console.error('获取留言列表失败:', error);
        res.status(500).json({
            success: false,
            message: '获取留言列表失败'
        });
    }
});

// 处理博客订阅
router.post('/messages/subscribe', async (req, res) => {
    try {
        const { name, email, phone, subject, message, subscription_type, blog_categories, source } = req.body;
        
        // 验证必填字段
        if (!name || !email || !message) {
            return res.status(400).json({
                success: false,
                message: '姓名、邮箱和留言内容为必填项'
            });
        }

        // 确定订阅类型和主题
        const finalSubscriptionType = subscription_type || 'blog';
        const finalSubject = subject || (finalSubscriptionType === 'blog' ? '博客订阅' : '普通留言');

        // 插入订阅数据到数据库
        const query = `
            INSERT INTO messages 
            (name, email, phone, subject, content, subscription_type, blog_categories, source, status, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending', NOW())
        `;
        
        const values = [
            name,
            email,
            phone || '',
            finalSubject,
            message,
            finalSubscriptionType,
            blog_categories ? JSON.stringify(blog_categories) : '[]',
            source || 'user-client'
        ];

        const [result] = await db.query(query, values);
        
        res.json({
            success: true,
            message: '订阅成功',
            data: {
                id: result.insertId,
                name,
                email,
                subscription_type: finalSubscriptionType,
                subject: finalSubject,
                status: 'pending'
            }
        });
        
    } catch (error) {
        console.error('处理博客订阅失败:', error);
        res.status(500).json({
            success: false,
            message: '订阅失败，请稍后重试'
        });
    }
});

// 获取订阅列表
router.get('/subscriptions', async (req, res) => {
    try {
        const { page = 1, limit = 10, search = '' } = req.query;
        const offset = (page - 1) * limit;
        
        let whereClause = '';
        let queryParams = [];
        
        if (search) {
            whereClause = `WHERE s.email LIKE ? OR s.preferences LIKE ? OR u.name LIKE ? OR u.email LIKE ? OR u.phone LIKE ? OR u.mobile LIKE ? OR u.address LIKE ?`;
            queryParams = [`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`];
        }
        
        // 获取总数
        const [totalResult] = await db.query(
            `SELECT COUNT(*) as total FROM subscriptions ${whereClause}`,
            queryParams
        );
        
        // 获取数据 - 包含完整的客户信息
        const [subscriptions] = await db.query(
            `SELECT 
                s.*,
                u.name as customer_name,
                u.email as customer_email,
                u.phone as customer_phone,
                u.mobile as customer_mobile,
                u.address as customer_address,
                u.city as customer_city,
                u.country as customer_country
            FROM subscriptions s
            LEFT JOIN users u ON s.user_id = u.id
            ${whereClause} ORDER BY s.created_at DESC LIMIT ? OFFSET ?`,
            [...queryParams, parseInt(limit), offset]
        );
        
        res.json({
            success: true,
            data: {
                subscriptions: subscriptions,
                total: totalResult.total,
                page: parseInt(page),
                totalPages: Math.ceil(totalResult.total / limit)
            }
        });
    } catch (error) {
        console.error('获取订阅列表失败:', error);
        res.status(500).json({
            success: false,
            message: '获取订阅列表失败'
        });
    }
});

// 批量操作留言
router.post('/messages/batch', async (req, res) => {
    try {
        const { action, ids } = req.body;
        
        if (!ids || !Array.isArray(ids) || ids.length === 0) {
            return res.status(400).json({
                success: false,
                message: '请选择要操作的留言'
            });
        }
        
        let updateQuery = '';
        let updateParams = [];
        
        switch (action) {
            case 'mark_read':
                updateQuery = 'UPDATE messages SET is_read = 1 WHERE id IN (?)';
                updateParams = [ids];
                break;
            case 'mark_unread':
                updateQuery = 'UPDATE messages SET is_read = 0 WHERE id IN (?)';
                updateParams = [ids];
                break;
            case 'delete':
                updateQuery = 'DELETE FROM messages WHERE id IN (?)';
                updateParams = [ids];
                break;
            default:
                return res.status(400).json({
                    success: false,
                    message: '不支持的操作类型'
                });
        }
        
        const [result] = await db.query(updateQuery, updateParams);
        
        res.json({
            success: true,
            message: `成功${action === 'delete' ? '删除' : '更新'} ${result.affectedRows} 条留言`
        });
    } catch (error) {
        console.error('批量操作留言失败:', error);
        res.status(500).json({
            success: false,
            message: '批量操作失败'
        });
    }
});

// 批量操作订阅
router.post('/subscriptions/batch', async (req, res) => {
    try {
        const { action, ids } = req.body;
        
        if (!ids || !Array.isArray(ids) || ids.length === 0) {
            return res.status(400).json({
                success: false,
                message: '请选择要操作的订阅'
            });
        }
        
        let updateQuery = '';
        let updateParams = [];
        
        switch (action) {
            case 'activate':
                updateQuery = 'UPDATE subscriptions SET status = "active" WHERE id IN (?)';
                updateParams = [ids];
                break;
            case 'deactivate':
                updateQuery = 'UPDATE subscriptions SET status = "inactive" WHERE id IN (?)';
                updateParams = [ids];
                break;
            case 'delete':
                updateQuery = 'DELETE FROM subscriptions WHERE id IN (?)';
                updateParams = [ids];
                break;
            default:
                return res.status(400).json({
                    success: false,
                    message: '不支持的操作类型'
                });
        }
        
        const [result] = await db.query(updateQuery, updateParams);
        
        res.json({
            success: true,
            message: `成功${action === 'delete' ? '删除' : '更新'} ${result.affectedRows} 条订阅`
        });
    } catch (error) {
        console.error('批量操作订阅失败:', error);
        res.status(500).json({
            success: false,
            message: '批量操作失败'
        });
    }
});

// 获取留言统计
router.get('/messages/stats', async (req, res) => {
    try {
        const [stats] = await db.query(`
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) as unread,
                SUM(CASE WHEN is_read = 1 THEN 1 ELSE 0 END) as read,
                SUM(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as last_7_days
            FROM messages
        `);
        
        res.json({
            success: true,
            data: stats[0]
        });
    } catch (error) {
        console.error('获取留言统计失败:', error);
        res.status(500).json({
            success: false,
            message: '获取统计信息失败'
        });
    }
});

// 获取订阅统计
router.get('/subscriptions/stats', async (req, res) => {
    try {
        const [stats] = await db.query(`
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive,
                SUM(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as last_7_days
            FROM subscriptions
        `);
        
        res.json({
            success: true,
            data: stats[0]
        });
    } catch (error) {
        console.error('获取订阅统计失败:', error);
        res.status(500).json({
            success: false,
            message: '获取统计信息失败'
        });
    }
});

module.exports = router;